﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DBhelper
    {
        public static string ConString = "server=.;database=ERP;uid=sa;pwd=1";
        public static SqlConnection Conn;
        public static void InitConnection()
        {
            //如果连接对象不存在
            if (Conn == null)
            {
                Conn = new SqlConnection(ConString);
            }
            if (Conn.State == ConnectionState.Closed)
            {
                Conn.Open();
            }
            if (Conn.State == ConnectionState.Broken)
            {
                Conn.Close();
                Conn.Open();
            }
        }
        /// <summary>
        /// 登录 查
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable SqlDatatable(string sql)
        {
            //创建对象
            Conn = new SqlConnection(ConString);
            //打开连接对象
            Conn.Open();
            //
            DataTable dt = new DataTable();//创建数据集对象
            SqlDataAdapter ada = new SqlDataAdapter(sql, Conn);
            //将数据集适配器获取到的表填充到数据集中
            ada.Fill(dt);
            //关闭连接
            Conn.Close();
            return dt;
        }

        /// <summary>
        /// 注册 增
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static bool Register(string userName, string Userpwd, string Useremail, string Userphone, string Usercompany, string Useraddress)
        {
            //创建连接
            SqlConnection conn = new SqlConnection(ConString);
            string sql = string.Format("insert into users(userName,Userpwd,Useremail,Userphone,Usercompany,Useraddress) values('{0}','{1}','{2}','{3}','{4}','{5}')", userName, Userpwd, Useremail, Userphone, Usercompany, Useraddress);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            if (result > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 增 删 改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sqlStr)
        {
            InitConnection();
            SqlCommand cmd = new SqlCommand(sqlStr, Conn);
            int retule = cmd.ExecuteNonQuery();
            Conn.Close();
            return retule > 0;
        }
    }
}

